# coding: utf-8

"""
author: liy13
"""
import datetime
import os
import time

import xlrd


def datetime_slash(s):
    return datetime.datetime.strptime(s, '%Y/%m/%d %H:%M:%S')


def xls_date_month_format(cell_value):
    """使用Datetime存储%Y-%m格式的日期时将%d设置为1"""
    if isinstance(cell_value, str):
        dt = datetime.datetime.strptime(cell_value, '%Y/%m/%d')
    else:
        dt = xlrd.xldate_as_datetime(cell_value, 0)
    return dt.strftime('%Y-%m-01')


def remove_file_if_exists(file_name):
    if os.path.exists(file_name):
        os.remove(file_name)


def clean_useless_field(column_dict, useless_list):
    """将useless_list中的字段从column_dict中删除
    (不修改原始数据)
    """
    if not useless_list:
        return column_dict
    res = {k: v for k, v in column_dict.items() if k not in useless_list}
    return res


def update_column_dict(column_dict, format_method_dict, replace_method_dict, xls_table, line_number):
    """使用多个method_dict更新column_dict"""
    for k in column_dict:
        if format_method_dict and k in format_method_dict:  # 需要对数据进行格式化
            cell_value = xls_table.cell(line_number, column_dict[k]).value
            column_dict[k] = format_method_dict[k](cell_value)
        elif replace_method_dict and k in replace_method_dict:  # 需要对数据进行替换
            column_dict[k] = replace_method_dict[k](xls_table, line_number, column_dict[k])  # 传入该表格和该单元格坐标
        else:  # 不需要进行处理的原始字段
            column_dict[k] = xls_table.cell(line_number, column_dict[k]).value


def update_task_progress(task, progress):
    """更新任务进度"""
    task.progress = progress
    if progress == 100:     # 进度100%时同步更新状态
        task.status = 2
    task.save()


def column_list_to_dict(column_list):
    """生成形如{'name': 1, 'gender': 2, 'address': 3}的dict数据"""
    return {item: index for index, item in enumerate(column_list)}


def build_filename():
    return '/tmp/{}.xls'.format(str(time.time()).replace('.', ''))


def xls_to_database(file_name, django_model, column_list, useless_list=None, format_method_dict=None,
                    replace_method_dict=None, set_method_dict=None, staff_id_key='staff_id', operator=None,
                    unique_fields=None, response_ids=False):
    """
    django_model: Django数据模型，与待导入的xls文件对应
    column_list: column_list中item的名字要与model中对应  column_list = ['performance_type', 'team_name', 'position', 'name']
    useless_list: 不需要导入的数据，通常这些数据是方便用户查看的，例如“同时拥有工号和姓名的表格中的姓名列”
    format_method_dict: 需要格式化的字段与格式化方法
    replace_method_dict 需要替换的字段与替换方法
    staff_id_key: 目标Model中记录UserProfile的字段名
    """

    xls_table = xlrd.open_workbook(file_name).sheets()[0]  # 取xls文件的第一个sheet
    table_rows = xls_table.nrows
    column_dict_list = []
    current_index = 1

    try:    # 正常流程数据逐条导入
        for line_number in range(1, table_rows):   # 去掉表头，只处理数据
            column_dict = column_list_to_dict(column_list)
            # 使用format_method_dict和replace_method_dict携带的方法更新column_dict为待插入数据库的正确数据
            update_column_dict(column_dict, format_method_dict, replace_method_dict, xls_table, line_number)

            # column_dict['status'] = 1
            column_dict['operator'] = operator
            column_dict_list.append(column_dict)
        res_ids = []
        for index, column_dict in enumerate(column_dict_list):
            current_index = index
            # 清除不需要导入的数据，通常这些数据是方便用户查看的，例如“同时拥有工号和姓名的表格中的姓名列”
            defaults = clean_useless_field(column_dict, useless_list)

            if unique_fields:
                unique_dict = {}
                for k in unique_fields:
                    assert k in defaults.keys(), (
                        "unique_fields字段 {} 值不存在!".format(k)
                    )
                    unique_dict[k] = defaults.pop(k)
                obj, created = django_model.objects.update_or_create(
                    defaults=defaults,
                    **unique_dict
                )
            else:
                obj = django_model.objects.create(**defaults)
            res_ids.append(obj.id)
            if set_method_dict:
                for k, set_method in set_method_dict.items():
                    set_method(k, column_dict.get(k), obj, column_dict)

        remove_file_if_exists(file_name)
        res = {'status': 'success'}
        if response_ids:
            res['ids'] = res_ids
        return res
    except Exception as e:  # 报错后将错误行号反馈
        import traceback
        traceback.print_exc()
        return {
            'status': 'fail',
            'exception_message': ' '.join([str(i) for i in e.args]),
            'message': '第{}行的数据存在错误，请检查并从第{}行导入。'.format(current_index + 1, current_index + 1),
        }
